const mysql = require('mysql2/promise');
require('dotenv').config();

async function showTableStructure() {
    try {
        const connection = await mysql.createConnection({
            host: process.env.DB_HOST,
            user: process.env.DB_USER,
            password: process.env.DB_PASSWORD,
            database: process.env.DB_NAME
        });

        // 获取所有表名
        const [tables] = await connection.query(`
            SHOW TABLES
        `);

        console.log('\n=== 数据库表结构 ===\n');

        // 遍历每个表，显示其结构
        for (const table of tables) {
            const tableName = table[`Tables_in_${process.env.DB_NAME}`];
            
            // 获取表结构
            const [columns] = await connection.query(`
                SHOW COLUMNS FROM ${tableName}
            `);

            console.log(`\n表名: ${tableName}`);
            console.log('字段\t\t类型\t\t可空\t\t键\t\t默认值\t\t额外信息');
            console.log('-'.repeat(100));

            columns.forEach(column => {
                console.log(
                    `${column.Field.padEnd(16)}`+
                    `${column.Type.padEnd(16)}`+
                    `${column.Null.padEnd(16)}`+
                    `${(column.Key || '').padEnd(16)}`+
                    `${(column.Default || '').toString().padEnd(16)}`+
                    `${column.Extra}`
                );
            });

            // 获取外键信息
            const [foreignKeys] = await connection.query(`
                SELECT 
                    CONSTRAINT_NAME,
                    COLUMN_NAME,
                    REFERENCED_TABLE_NAME,
                    REFERENCED_COLUMN_NAME
                FROM information_schema.KEY_COLUMN_USAGE
                WHERE TABLE_SCHEMA = ? 
                AND TABLE_NAME = ?
                AND REFERENCED_TABLE_NAME IS NOT NULL
            `, [process.env.DB_NAME, tableName]);

            if (foreignKeys.length > 0) {
                console.log('\n外键约束:');
                foreignKeys.forEach(fk => {
                    console.log(
                        `${fk.CONSTRAINT_NAME}: `+
                        `${fk.COLUMN_NAME} -> `+
                        `${fk.REFERENCED_TABLE_NAME}(${fk.REFERENCED_COLUMN_NAME})`
                    );
                });
            }
        }

        await connection.end();

    } catch (error) {
        console.error('Error:', error);
    }
}

showTableStructure();